#!/usr/bin/env python
# coding=utf-8
"""
@Author  : youjia - 卞志伟
@file    : business_specify.py.py
@contact : bianzhiwei@iyoujia.com
@time    : 2019-08-08 16:35 
@Desc    : 
@Software: PyCharm
"""
import os
import sys

# 当前文件的路径
pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)

import pandas as pd
from report_system.utils import db_util
from report_system.utils import date_util
from report_system.utils import df_util
from report_system.utils.log_util import log
from report_system.utils.num_util import percentage


def house_base_data(own_type=(1, 2, 3)):
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """   select 
                case whs.own_type when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
                whs.city_name 城市,
                ifnull(whs.work_name,'门店为空') 门店,
                whs.room_num 居室,
                concat(whs.house_id,"") 房屋ID,
                concat(whs.lodge_id,"") 房源ID,
                whs.title 标题,
                date(whtn.online_time) 上线时间,
                whs.online_sale 线上管家,
                whs.offline_sale 线下销售,
                whs.invest_name 投资人
                from youjia_report.wh_house_static whs
                left join youjia_report.wh_house_time_node whtn on whs.house_id = whtn.house_id
                where whs.house_state =100
                and whs.lodge_state = 0
                and whs.own_type in {own_type}
                and whs.lodge_state_super = 10
            """.format(own_type=own_type)
    # print(sql)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def house_rent_day(dt=None):
    sql = """select 
             concat(whc.house_id,"") 房屋ID,
            if(whc.dt = '{dt}' and ((whc.online_stock=1 and whc.rent_stock=1) or have_order=1),1,0) 是否可租,
            if(whc.dt = '{dt}' and have_order =1,1,0) 是否已租,
            sum(if( (whc.dt between date_add('{dt}',interval 1 day) and date_add('{dt}',interval 3 day)) and have_order =1,1,0)) 未来3天已售间夜,
            sum(if( (whc.dt between date_add('{dt}',interval 1 day) and date_add('{dt}',interval 7 day)) and have_order =1,1,0)) 未来7天已售间夜,
            sum(if( (whc.dt between date_add('{dt}',interval 1 day) and date_add('{dt}',interval 14 day)) and have_order =1,1,0)) 未来14天已售间夜,
            sum(if( (whc.dt between date_add('{dt}',interval 1 day) and date_add('{dt}',interval 30 day)) and have_order =1,1,0)) 未来30天已售间夜
            from youjia_report.wh_house_calendar whc
            where whc.order_type<>3
            and whc.dt between '{dt}' and date_add('{dt}',interval 30 day)
            group by whc.house_id""".format(dt=dt)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def house_price(dt=None):
    import re

    def re_search(text):
        if '渠道id-' in text:
            return re.search('渠道id-(.*?),', text).group(1).replace(']', '')

        if '渠道id:' in text:
            return re.search('渠道id:(.*?),', text).group(1).replace(']', '')

        return None

    sql_lp = """select 
                concat(lh.id,"") 房屋ID,
                remark,date(lp.create_time) dt 
                from youjia.lod_operations lp
                left join youjia.lod_house lh on lh.lodge_id = lp.lodge_id
                where lp.ops_type in (3,4,5,10,11) 
                and date(lp.create_time) = '{dt}'  
                order by lp.create_time desc 
                 """.format(dt=dt)

    df = pd.read_sql_query(sql_lp, db_util.youjia_13_sjz_db)

    lp_list = list()
    for idx, row in df.iterrows():
        house_id = row['房屋ID']
        channel_id = re_search(row['remark'])
        dt = row['dt']
        if channel_id in ('3', '25', '20') and house_id != '' and house_id != None:
            zhenguo = 0
            airbnb = 0
            tujia = 0
            if str(channel_id) == '20':
                airbnb += 1
            if str(channel_id) == '25':
                zhenguo += 1
            if str(channel_id) == '3':
                tujia += 1

            lp_list.append({"房屋ID": house_id, "途家调价频次": tujia, "榛果调价频次": zhenguo, "Airbnb调价频次": airbnb})

    lp_df = pd.DataFrame(lp_list)
    lp_df = lp_df.groupby(["房屋ID"]).agg({'途家调价频次': 'sum', '榛果调价频次': 'sum', 'Airbnb调价频次': 'sum'})
    lp_df = lp_df.reset_index()

    sql_price = """select 
            concat(wlc.house_id,"") 房屋ID,
            sum(if(wlc.channel_id = 3, wlc.price/100,null)) 途家测算价,
            sum(if(wlc.channel_id = 20, wlc.price/100,null))  Airbnb测算价,
            sum(if(wlc.channel_id = 25, wlc.price/100,null))  榛果测算价,
            sum(if(wlc.channel_id = 3,sale.price ,null)) 途家前端价格,
            sum(if(wlc.channel_id = 20,sale.price ,null)) Airbnb前端价格,
            sum(if(wlc.channel_id = 25,sale.price ,null)) 榛果前端价格
            from youjia_report.wh_lodge_calendar wlc 
            left join youjia_tpp.sales_001 sale on sale.lodge_id = wlc.lodge_id and sale.dt = wlc.dt and sale.platform_id = wlc.channel_id
            left join youjia_common.sales_channel sc on sc.id = wlc.channel_id
            where wlc.dt = '{dt}'
            and wlc.channel_id in (3,25,20)
            and wlc.house_state = 100
            and wlc.lodge_state = 0 
            and wlc.lodge_state_super = 10
            group by wlc.house_id
        """.format(dt=dt)
    price_df = pd.read_sql_query(sql_price, db_util.youjia_13_sjz_db)
    df = pd.merge(price_df, lp_df, how='left', on=['房屋ID'])
    # pd.DataFrame.reset_index()
    # pd.DataFrame.unstack(fill_value='')
    return df


def house_sale_data(st, et):
    sql = """select 
        concat(whc.house_id,"") 房屋ID,
        concat(round(sum(if((online_stock=1 and rent_stock = 1) or have_order =1 ,1,0))/sum(if(online_stock=1 or have_order =1 ,1,0))*100),'%') 可租率,
        concat(round(sum(have_order)/sum(if((online_stock=1 and rent_stock = 1) or have_order =1 ,1,0))*100),'%') 可售房源入住率,
        concat(round(sum(if(whc.order_type <> 3,have_order,0))/sum(if(( online_stock=1  or have_order =1) and whc.order_type <> 3 ,1,0))*100),'%') 在线房源入住率,
        round(sum(income+compensate_income)/sum(have_order)/100) 日价,
        round(sum(income+compensate_income)/sum(if((whc.online_stock=1 and whc.rent_stock=1) or whc.have_order = 1,1,0))/100) REVPAR,
        round(sum(income+compensate_income)/100,2) GMV,
        round(((sum(income+compensate_income)*0.94*0.7 - sum(rent_day))/100)) 总净利
        
        from youjia_report.wh_house_calendar whc
        left join youjia_report.wh_house_static whs on whc.house_id = whs.house_id
        where dt between '{st}' and '{et}'
        and house_state = 100
        and lodge_state = 0
        and lodge_state_super = 10
        and order_type<>3
        group by whc.house_id""".format(st=st, et=et)
    return pd.read_sql_query(sql, db_util.con_report)


def channel_order(st, et, channel_id):
    sql = """select 
                concat(od.house_id,"") 房屋ID,
               
                sum( if(date(bo.checkout_day) between  '{st}' and '{et}' and bo.order_channel = {channel_id} and bo.brush_status <> 1 ,1,null)) 真实订单数（离店发生在本月）,
                sum(if(bo.order_channel = {channel_id} and bo.brush_status <> 1 ,datediff(date(if(bo.checkout_day >'{et}','{et}',bo.checkout_day)),
                             date(if(bo.checkin_day <'{st}','{st}',bo.checkin_day))),null)) 真实入住间夜数,
                concat(round(sum(if(bo.order_channel = {channel_id} and bo.brush_status <> 1 ,datediff(date(if(bo.checkout_day >'{et}','{et}',bo.checkout_day)),
                             date(if(bo.checkin_day <'{st}','{st}',bo.checkin_day))),null))*100/
                sum(if(bo.brush_status <> 1 , datediff(date(if(bo.checkout_day >'{et}','{et}',bo.checkout_day)),
                             date(if(bo.checkin_day <'{st}','{st}',bo.checkin_day))),0))),'%') 间夜占比,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                    and bo.order_channel = {channel_id} and bo.brush_status <> 1  
                                    and ct.score <> '' ,1,null)) 真实评价数,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )  
                                    and bo.order_channel = {channel_id} and bo.brush_status <> 1 
                                    and ct.score = 5 ,1,null)) 好评,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                    and bo.order_channel = {channel_id} and bo.brush_status <> 1  
                                    and ct.score <=4 ,1,null)) 差评,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                    and bo.order_channel = {channel_id} and bo.brush_status <> 1  
                                    and ct2.content is not null,1,null)) 评价回复数,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                    and bo.order_channel = {channel_id} and bo.brush_status = 1  ,1,null)) 刷单数,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                    and bo.order_channel = {channel_id} and bo.brush_status = 1  
                                    and ct.content is not null,1,null)) 刷单评价数
                
                from youjia.orders bo 
                left join youjia.order_detail od on od.order_id = bo.id
                left join youjia_common.sales_channel sc on sc.id = bo.order_channel
                left join youjia.comment ct on ct.order_id = bo.id and ct.auto_comment = 0 and ct.comment_type = 0
                left join youjia.comment ct2 on ct2.order_id = bo.id and ct2.auto_comment = 0 and ct2.comment_type = 1
                where bo.special_status <> 3 
                and bo.cancel_status in (0,4)
                and bo.city_id <> 376
                AND (bo.pay_state = 1  OR (bo.pay_state = 0 AND  bo.order_channel=5)) 
                and bo.order_type <> 3 
                and date(bo.actcheckout_day) >=  '{st}' 
                and date(bo.actcheckin_day) <= '{et}' 
                group by od.house_id 
    ;""".format(st=st, et=et, channel_id=channel_id)
    # print(sql)
    return pd.read_sql_query(sql, db_util.con_youjia)


def month_order_work(st, et, own_type=(1, 2, 3)):
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
                
                if (COALESCE(lh.work_id,'全国') ='全国','全国',group_concat(distinct city.short_name)) 城市,
				case group_concat(distinct lh.own_type order by lh.own_type) 
				    when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
				if (COALESCE(lh.work_id,'全国') ='全国','全国',group_concat(distinct uo.name)) 门店,
                sum( if(date(bo.checkout_day) between  '{st}' and '{et}'  and bo.brush_status <> 1 ,1,null)) 真实订单数（离店发生在本月）,
                sum(if(bo.brush_status <> 1 and date(bo.actcheckout_day) >=  '{st}' and date(bo.actcheckin_day) <= '{et}',
					datediff(date(if(date(bo.actcheckout_day) >='{et}','{et}',date(bo.actcheckout_day))),
                     date(if(date(bo.actcheckin_day) <='{st}','{st}',date(bo.actcheckin_day)))),null)) 真实入住间夜数,
				 
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                     and bo.brush_status <> 1  
                                    and ct.score <> '' ,1,null)) 真实评价数,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )  
                                    and bo.brush_status <> 1 
                                    and ct.score = 5 ,1,null)) 好评,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                    and bo.brush_status <> 1  
                                    and ct.score <=4 ,1,null)) 差评,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                    and bo.brush_status <> 1  
                                    and ct2.content is not null,1,null)) 评价回复数,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                    and bo.brush_status = 1  ,1,null)) 刷单数,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                    and bo.brush_status = 1  
                                    and ct.content is not null,1,null)) 刷单评价数
                
                from youjia.orders bo 
                left join youjia.order_detail od on od.order_id = bo.id
                left join youjia.lod_house lh on lh.id = od.house_id
                left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id
                left join youjia_common.sales_channel sc on sc.id = bo.order_channel
                left join youjia.comment ct on ct.order_id = bo.id and ct.auto_comment = 0 and ct.comment_type = 0
                left join youjia.comment ct2 on ct2.order_id = bo.id and ct2.auto_comment = 0 and ct2.comment_type = 1
                left join youjia.user_org uo on uo.id = lh.work_id
                LEFT JOIN youjia.city on city.id = lh.city_id
                where bo.special_status <> 3 
                and bo.cancel_status in (0,4)
                AND (bo.pay_state = 1  OR (bo.pay_state = 0 AND  bo.order_channel=5)) 
                and bo.order_type <> 3 
                and bo.city_id <> 376
                and lh.city_id <> 376
                and lh.own_type in {own_type}
                and ((date(bo.actcheckout_day) >=  '{st}' and date(bo.actcheckin_day) <= '{et}' ) 
					  or date(bo.create_time) between '{st}' and '{et}'  )
                
                group by lh.own_type,lh.work_id
                with rollup
                ;""".format(st=st, et=et, own_type=own_type)
    # print(sql)
    return pd.read_sql_query(sql, db_util.con_youjia)


def month_order_service(st, et, own_type=(1, 2, 3)):
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
                if(COALESCE(lh.service_id,'全国') = '全国','全国',city.short_name)  城市,
                case group_concat(distinct lh.own_type order by lh.own_type) when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
                if(COALESCE(lh.service_id,'全国') = '全国','全国',ifnull(uosc.sname,"服务中心为空")) 服务中心,
                sum( if(date(bo.checkout_day) between  '{st}' and '{et}'  and bo.brush_status <> 1 ,1,null)) 真实订单数（离店发生在本月）,
                sum(if(bo.brush_status <> 1 and date(bo.actcheckout_day) >=  '{st}' and date(bo.actcheckin_day) <= '{et}',
					datediff(date(if(date(bo.actcheckout_day) >='{et}','{et}',date(bo.actcheckout_day))),
                     date(if(date(bo.actcheckin_day) <='{st}','{st}',date(bo.actcheckin_day)))),null)) 真实入住间夜数,
				 
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                     and bo.brush_status <> 1  
                                    and ct.score <> '' ,1,null)) 真实评价数,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )  
                                    and bo.brush_status <> 1 
                                    and ct.score = 5 ,1,null)) 好评,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                    and bo.brush_status <> 1  
                                    and ct.score <=4 ,1,null)) 差评,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                    and bo.brush_status <> 1  
                                    and ct2.content is not null,1,null)) 评价回复数,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                    and bo.brush_status = 1  ,1,null)) 刷单数,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                    and bo.brush_status = 1  
                                    and ct.content is not null,1,null)) 刷单评价数
                
                from youjia.orders bo 
                left join youjia.order_detail od on od.order_id = bo.id
                left join youjia.lod_house lh on lh.id = od.house_id
                left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id
                left join youjia_common.sales_channel sc on sc.id = bo.order_channel
                left join youjia.comment ct on ct.order_id = bo.id and ct.auto_comment = 0 and ct.comment_type = 0
                left join youjia.comment ct2 on ct2.order_id = bo.id and ct2.auto_comment = 0 and ct2.comment_type = 1
                left join youjia.city on city.id = lh.city_id
                where bo.special_status <> 3 
                and bo.cancel_status in (0,4)
                AND (bo.pay_state = 1  OR (bo.pay_state = 0 AND  bo.order_channel=5)) 
                and bo.order_type <> 3 
                and bo.city_id <> 376
                and lh.city_id <> 376
                and lh.own_type in {own_type}
                and ((date(bo.actcheckout_day) >=  '{st}' and date(bo.actcheckin_day) <= '{et}' ) 
					  or date(bo.create_time) between '{st}' and '{et}'  )
                
                group by  lh.own_type,lh.service_id with rollup;""".format(st=st, et=et, own_type=own_type)
    # print(sql)
    return pd.read_sql_query(sql, db_util.con_youjia)


def month_order_city(st, et, own_type=(1, 2, 3)):
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
                 
                COALESCE(city.short_name,'全国')  城市,  
                case group_concat(distinct lh.own_type order by lh.own_type)
                    when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
                sum( if(date(bo.checkout_day) between  '{st}' and '{et}'  and bo.brush_status <> 1 ,1,null)) 真实订单数（离店发生在本月）,
                sum(if(bo.brush_status <> 1 and date(bo.actcheckout_day) >=  '{st}' and date(bo.actcheckin_day) <= '{et}',
					datediff(date(if(date(bo.actcheckout_day) >='{et}','{et}',date(bo.actcheckout_day))),
                     date(if(date(bo.actcheckin_day) <='{st}','{st}',date(bo.actcheckin_day)))),null)) 真实入住间夜数,
				 
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                     and bo.brush_status <> 1  
                                    and ct.score <> '' ,1,null)) 真实评价数,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )  
                                    and bo.brush_status <> 1 
                                    and ct.score = 5 ,1,null)) 好评,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                    and bo.brush_status <> 1  
                                    and ct.score <=4 ,1,null)) 差评,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                    and bo.brush_status <> 1  
                                    and ct2.content is not null,1,null)) 评价回复数,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                    and bo.brush_status = 1  ,1,null)) 刷单数,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                    and bo.brush_status = 1  
                                    and ct.content is not null,1,null)) 刷单评价数
                
                from youjia.orders bo 
                left join youjia.order_detail od on od.order_id = bo.id
                left join youjia.lod_house lh on lh.id = od.house_id
                left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id
                left join youjia_common.sales_channel sc on sc.id = bo.order_channel
                left join youjia.comment ct on ct.order_id = bo.id and ct.auto_comment = 0 and ct.comment_type = 0
                left join youjia.comment ct2 on ct2.order_id = bo.id and ct2.auto_comment = 0 and ct2.comment_type = 1
                left join youjia.city on city.id = bo.city_id
                where bo.special_status <> 3 
                and bo.cancel_status in (0,4)
                AND (bo.pay_state = 1  OR (bo.pay_state = 0 AND  bo.order_channel=5)) 
                and bo.order_type <> 3 
                and bo.city_id <> 376
                and lh.own_type in {own_type}
                and ((date(bo.actcheckout_day) >=  '{st}' and date(bo.actcheckin_day) <= '{et}' ) 
					  or date(bo.create_time) between '{st}' and '{et}'  )
                
                group by  lh.own_type,city.short_name with rollup;""".format(st=st, et=et, own_type=own_type)
    # print(sql)
    return pd.read_sql_query(sql, db_util.con_youjia)


def month_order_online(st, et, own_type=(1, 2, 3)):
    sql = """select 
                if(COALESCE(u.id,'全国')='全国','全国',city.short_name)  城市,  
                case group_concat(distinct lh.own_type order by lh.own_type)
                    when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
                if(COALESCE(u.id,'全国')='全国','全国',ifnull(u.realname,'门店销售为空')) 门店销售,
                sum( if(date(bo.checkout_day) between  '{st}' and '{et}'  and bo.brush_status <> 1 ,1,null)) 真实订单数（离店发生在本月）,
                sum(if(bo.brush_status <> 1 and date(bo.actcheckout_day) >=  '{st}' and date(bo.actcheckin_day) <= '{et}',
					datediff(date(if(date(bo.actcheckout_day) >='{et}','{et}',date(bo.actcheckout_day))),
                     date(if(date(bo.actcheckin_day) <='{st}','{st}',date(bo.actcheckin_day)))),null)) 真实入住间夜数,
				 
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                     and bo.brush_status <> 1  
                                    and ct.score <> '' ,1,null)) 真实评价数,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )  
                                    and bo.brush_status <> 1 
                                    and ct.score = 5 ,1,null)) 好评,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                    and bo.brush_status <> 1  
                                    and ct.score <=4 ,1,null)) 差评,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                    and bo.brush_status <> 1  
                                    and ct2.content is not null,1,null)) 评价回复数,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                    and bo.brush_status = 1  ,1,null)) 刷单数,
                sum( if((date(bo.checkout_day) between  '{st}' and '{et}' )
                                    and bo.brush_status = 1  
                                    and ct.content is not null,1,null)) 刷单评价数
                
                from youjia.orders bo 
                left join youjia.order_detail od on od.order_id = bo.id
                left join youjia.lod_house lh on lh.id = od.house_id
                left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id
                left join youjia_common.sales_channel sc on sc.id = bo.order_channel
                left join youjia.comment ct on ct.order_id = bo.id and ct.auto_comment = 0 and ct.comment_type = 0
                left join youjia.comment ct2 on ct2.order_id = bo.id and ct2.auto_comment = 0 and ct2.comment_type = 1
                left join youjia.city on city.id = bo.city_id
                left join youjia.user_house_relation uhr on uhr.house_id = lh.id and uhr.user_role_id = 5 and uhr.is_delete = 0 and uhr.master = 1
                left join youjia.users u on u.id = uhr.user_id
                where bo.special_status <> 3 
                and bo.cancel_status in (0,4)
                AND (bo.pay_state = 1  OR (bo.pay_state = 0 AND  bo.order_channel=5)) 
                and bo.order_type <> 3 
                and bo.city_id <> 376
                and lh.own_type in {own_type}
                and ((date(bo.actcheckout_day) >=  '{st}' and date(bo.actcheckin_day) <= '{et}' ) 
					  or date(bo.create_time) between '{st}' and '{et}'  )
                
                group by  lh.own_type,u.id with rollup;""".format(st=st, et=et, own_type=own_type)
    # print(sql)
    return pd.read_sql_query(sql, db_util.con_youjia)

# month_order('2019-08-01', '2019-08-19', 20)

def airbnb_tag(dt):
    sql = """
        SELECT 
        concat(lh.id,"") 房屋ID,
        sum(if(name ='超赞房东',1,null)) 超赞房东,
        sum(if(name ='优质房源',1,null)) 优质房源,
        sum(if(name ='闪订',1,null)) 闪订
        FROM youjia_tpp.sales_001_tag s1t
        left join youjia_tpp.sales_001  s1 on s1t.lodge_id = s1.lodge_id and  s1t.lodge_id = s1.lodge_id and s1t.dt = s1.dt
        and s1t.platform_id = s1.platform_id
        left join youjia.lod_house lh on lh.lodge_id = s1t.lodge_id
        where s1t.platform_id = 20 and  s1t.dt = '{dt}'
        group by  s1t.lodge_id
        """.format(dt=dt)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def tujia_tag(dt):
    sql = """SELECT 
        concat(lh.id,"") 房屋ID,
        sum(if(name ='优选',1,null)) 优选,
        sum(if(name ='闪订',1,null)) 闪订,
        sum(if(name ='连住优惠',1,null)) 连住优惠,
        sum(if(name ='优选PRO',1,null)) 优选PRO,
        sum(if(name ='实拍',1,null)) 实拍
        FROM youjia_tpp.sales_001_tag s1t
        left join youjia_tpp.sales_001  s1 on s1t.lodge_id = s1.lodge_id and  s1t.lodge_id = s1.lodge_id and s1t.dt = s1.dt
        and s1t.platform_id = s1.platform_id
        left join youjia.lod_house lh on lh.lodge_id = s1t.lodge_id
        where s1t.platform_id = 3 and  s1t.dt = '{dt}'
        group by  s1t.lodge_id
        """.format(dt=dt)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def zhenguo_tag(dt):
    sql = """
            SELECT 
            concat(lh.id,"") 房屋ID,
            sum(if(name ='立即确认',1,null)) 立即确认,
            sum(if(name ='超赞房东',1,null)) 超赞房东,
            sum(if(name ='新房特惠',1,null)) 新房特惠,
            sum(if(name ='夏季特惠',1,null)) 夏季特惠,
            sum(if(name ='TOP美宿',1,null)) TOP美宿,
            sum(if(name ='首单特惠',1,null)) 首单特惠,
            sum(if(name ='连住优惠',1,null)) 连住优惠,
            sum(if(name ='今夜特价',1,null)) 今夜特价,
            sum(if(name ='限时折扣',1,null)) 限时折扣
            FROM youjia_tpp.sales_001_tag s1t
        left join youjia_tpp.sales_001  s1 on s1t.lodge_id = s1.lodge_id and  s1t.lodge_id = s1.lodge_id and s1t.dt = s1.dt
        and s1t.platform_id = s1.platform_id
        left join youjia.lod_house lh on lh.lodge_id = s1t.lodge_id
        where s1t.platform_id = 25 and  s1t.dt = '{dt}'
        group by  s1t.lodge_id
        """.format(dt=dt)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def online_sale_data(st, et, own_type=(1, 2, 3)):
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
        whc.dt 日期,
        if(COALESCE(whs.online_sale,'全国')='全国','全国',group_concat(distinct whs.city_name))  城市,
        case group_concat(distinct whs.own_type order by whs.own_type)
            when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
        if(COALESCE(whs.online_sale,'全国')='全国','全国',group_concat(distinct ifnull(whs.work_name,'门店为空'))) 门店,
        if(COALESCE(whs.online_sale,'全国')='全国','全国',group_concat(distinct ifnull(whs.online_sale,'门店销售为空'))) 门店销售,
        
        count(distinct if(whc.online_stock=1 or (have_order=1 and whc.order_type <> 3),whs.house_id,null)) 房量,
        concat(round(sum(if((whc.online_stock=1 and whc.rent_stock=1) or (whc.have_order = 1 and whc.order_type <> 3),1,0))/sum(if(whc.online_stock=1 or (whc.have_order = 1 and whc.order_type <> 3),1,0))*100),'%')  可租率,
        concat(round(sum(if(whc.order_type <> 3,have_order,0))/sum(if(((online_stock=1 and rent_stock = 1) or have_order =1) and whc.order_type <> 3 ,1,0))*100),'%') 可售房源入住率,
        concat(round(sum(if(whc.order_type <> 3,have_order,0))/sum(if(( online_stock=1  or have_order =1) and whc.order_type <> 3 ,1,0))*100),'%') 在线房源入住率,
        count(distinct if(order_time = dt and whc.order_type <> 3,whc.order_id,null)) 预定订单中当日入住订单,
        sum(order_num) 预定订单,
        sum(order_night) 预定订单间夜,
        round(sum(if( whc.order_type <> 3,income + compensate_income,0))/100) GMV,
        round(sum(if( whc.order_type <> 3,income + compensate_income,0))/sum(if( whc.order_type <> 3,have_order,0))/100) 日价,
        round(((sum(if( whc.order_type <> 3,income + compensate_income,0))*0.94*0.7 - sum(if( whc.order_type <> 3,rent_day,0)))/100)) 总净利,
        round(sum(if( whc.order_type <> 3,income + compensate_income,0))/sum(if((whc.online_stock=1 and whc.rent_stock=1) or (whc.have_order = 1 and whc.order_type <> 3),1,0))/100) REVPAR
        from youjia_report.wh_house_static whs
        left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
        where whc.dt  between '{st}' and '{et}'
        and whs.own_type in {own_type}
        and whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
        group by  whs.own_type,whs.online_sale with rollup""".format(st=st, et=et, own_type=own_type)
    return pd.read_sql_query(sql, db_util.con_youjia)


def service_sale_data(st, et, own_type=(1, 2, 3)):
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
        whc.dt 日期,
        if(COALESCE(whs.service_id,'全国')='全国','全国',group_concat(distinct whs.city_name))  城市,
         case group_concat(distinct whs.own_type order by whs.own_type)
         when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
        if(COALESCE(whs.service_id,'全国')='全国','全国',group_concat(distinct ifnull(whs.service_name,"服务中心为空"))) 服务中心,
        count(distinct if(whc.online_stock=1 or (have_order=1 and whc.order_type <> 3),whs.house_id,null)) 房量,
        concat(round(sum(if((whc.online_stock=1 and whc.rent_stock=1) or (whc.have_order = 1 and whc.order_type <> 3),1,0))/sum(if(whc.online_stock=1 or (whc.have_order = 1 and whc.order_type <> 3),1,0))*100),'%')  可租率,
        concat(round(sum(if(whc.order_type <> 3,have_order,0))/sum(if(((online_stock=1 and rent_stock = 1) or have_order =1) and whc.order_type <> 3 ,1,0))*100),'%') 可售房源入住率,
        concat(round(sum(if(whc.order_type <> 3,have_order,0))/sum(if(( online_stock=1  or have_order =1) and whc.order_type <> 3 ,1,0))*100),'%') 在线房源入住率,
        count(distinct if(order_time = dt and whc.order_type <> 3,whc.order_id,null)) 预定订单中当日入住订单,
        sum(order_num) 预定订单,
        sum(order_night) 预定订单间夜,
        round(sum(if( whc.order_type <> 3,income + compensate_income,0))/100) GMV,
        round(sum(if( whc.order_type <> 3,income+ compensate_income,0))/sum(if( whc.order_type <> 3,have_order,0))/100) 日价,
        round(((sum(if( whc.order_type <> 3,income + compensate_income,0))*0.94*0.7 - sum(if( whc.order_type <> 3,rent_day,0)))/100)) 总净利,
        round(sum(if( whc.order_type <> 3,income+ compensate_income,0))/sum(if((whc.online_stock=1 and whc.rent_stock=1) or (whc.have_order = 1 and whc.order_type <> 3),1,0))/100) REVPAR
        from youjia_report.wh_house_static whs
        left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
        where whc.dt  between '{st}' and '{et}'
        and whs.own_type in {own_type}
        and whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
        group by  whs.own_type,whs.service_id with rollup""".format(st=st, et=et, own_type=own_type)
    return pd.read_sql_query(sql, db_util.con_youjia)


def service_sale_data_last_year(st, et, own_type=(1, 2, 3)):
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    st = date_util.date_sub(st, years=1, days=0)
    et = date_util.date_sub(et, years=1, days=0)
    sql = """select 
            if(COALESCE(whs.service_id,'全国') ='全国','全国',group_concat(distinct whs.city_name))  城市,
             case group_concat(distinct whs.own_type order by whs.own_type)
             when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
             if(COALESCE(whs.service_id,'全国') ='全国','全国',group_concat(distinct ifnull(whs.service_name,"服务中心为空") ))服务中心,
            concat(round(sum(have_order)/sum(if(online_stock=1  or have_order =1,1,0))*100),'%') 去年同期在线房源入住率,
            round(sum(income+compensate_income)/sum(have_order)/100) 去年同期日价,
            round(sum(income+compensate_income)/sum(if((whc.online_stock=1 and whc.rent_stock=1) or (whc.have_order = 1 ),1,0))/100) 去年同期REVPAR
            from youjia_report.wh_house_static whs
            left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
            where whc.dt  between '{st}' and '{et}'
            and (whc.online_stock = 1 or have_order = 1)
             and whs.own_type in {own_type}
            group by  whs.own_type,whs.service_id with rollup""".format(st=st, et=et, own_type=own_type)
    return pd.read_sql_query(sql, db_util.con_youjia)


def work_sale_data_last_year(st, et, own_type=(1, 2, 3)):
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    st = date_util.date_sub(st, years=1, days=0)
    et = date_util.date_sub(et, years=1, days=0)
    sql = """select 
         if(COALESCE(whs.work_name,'全国') ='全国','全国',group_concat(distinct whs.city_name))  城市,
             case group_concat(distinct whs.own_type order by whs.own_type)
             when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
        if(COALESCE(whs.work_name,'全国') ='全国','全国',group_concat(distinct whs.work_name)) 门店,
        concat(round(sum(have_order)/sum(if(online_stock=1  or have_order =1,1,0))*100),'%') 去年同期在线房源入住率,
        round(sum(income+compensate_income)/sum(have_order)/100) 去年同期日价,
        round(sum(income+compensate_income)/sum(if((whc.online_stock=1 and whc.rent_stock=1) or (whc.have_order = 1 ),1,0))/100) 去年同期REVPAR
        from youjia_report.wh_house_static whs
        left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
        where whc.dt  between '{st}' and '{et}'
        and whs.own_type in {own_type}
        and (whc.online_stock = 1 or have_order = 1)
        group by whs.own_type,whs.work_name with rollup""".format(st=st, et=et, own_type=own_type)
    return pd.read_sql_query(sql, db_util.con_youjia)


def work_sale_data(st, et, own_type=(1, 2, 3)):
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
        whc.dt 日期,
        if(COALESCE(whs.work_name,'全国') ='全国','全国',group_concat(distinct whs.city_name))  城市,
             case group_concat(distinct whs.own_type order by whs.own_type)
             when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
        
        if(COALESCE(whs.work_name,'全国') ='全国','全国',group_concat(distinct whs.work_name)) 门店,
        
        count(distinct if(whc.online_stock=1 or (have_order=1 and whc.order_type <> 3),whs.house_id,null)) 房量,
        concat(round(sum(if((whc.online_stock=1 and whc.rent_stock=1) or (whc.have_order = 1 and whc.order_type <> 3),1,0))/sum(if(whc.online_stock=1 or (whc.have_order = 1 and whc.order_type <> 3),1,0))*100),'%')  可租率,
        concat(round(sum(if(whc.order_type <> 3,have_order,0))/sum(if(((online_stock=1 and rent_stock = 1) or have_order =1) and whc.order_type <> 3 ,1,0))*100),'%') 可售房源入住率,
        concat(round(sum(if(whc.order_type <> 3,have_order,0))/sum(if(( online_stock=1  or have_order =1) and whc.order_type <> 3 ,1,0))*100),'%') 在线房源入住率,
        count(distinct if(order_time = dt and whc.order_type <> 3,whc.order_id,null)) 预定订单中当日入住订单,
        sum(order_num) 预定订单,
        sum(order_night) 预定订单间夜,
        round(sum(if( whc.order_type <> 3,income + compensate_income,0))/100) GMV,
        round(sum(if( whc.order_type <> 3,income + compensate_income,0))/sum(if( whc.order_type <> 3,have_order,0))/100) 日价,
        round(((sum(if( whc.order_type <> 3,income + compensate_income,0))*0.94*0.7 - sum(if( whc.order_type <> 3,rent_day,0)))/100)) 总净利,
        round(sum(if( whc.order_type <> 3,income+ compensate_income,0))/sum(if((whc.online_stock=1 and whc.rent_stock=1) or (whc.have_order = 1 and whc.order_type <> 3),1,0))/100) REVPAR
        from youjia_report.wh_house_static whs
        left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
        where whc.dt  between '{st}' and '{et}'
        and whs.own_type in {own_type}
        and whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
        group by whs.own_type,whs.work_name with rollup""".format(st=st, et=et, own_type=own_type)
    return pd.read_sql_query(sql, db_util.con_youjia)


def city_sale_data(st, et, own_type=(1, 2, 3)):
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
        whc.dt 日期,
        COALESCE(whs.city_name,'全国')  城市,
        case group_concat(distinct whs.own_type order by whs.own_type)
             when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
        count(distinct if(whc.online_stock=1 or (have_order=1 and whc.order_type <> 3),whs.house_id,null)) 房量,
        concat(round(sum(if((whc.online_stock=1 and whc.rent_stock=1) or (whc.have_order = 1 and whc.order_type <> 3),1,0))/sum(if(whc.online_stock=1 or (whc.have_order = 1 and whc.order_type <> 3),1,0))*100),'%')  可租率,
        concat(round(sum(if(whc.order_type <> 3,have_order,0))/sum(if(((online_stock=1 and rent_stock = 1) or have_order =1) and whc.order_type <> 3 ,1,0))*100),'%') 可售房源入住率,
        concat(round(sum(if(whc.order_type <> 3,have_order,0))/sum(if(( online_stock=1  or have_order =1) and whc.order_type <> 3 ,1,0))*100),'%') 在线房源入住率,
        count(distinct if(order_time = dt and whc.order_type <> 3,whc.order_id,null)) 预定订单中当日入住订单,
        sum(order_num) 预定订单,
        sum(order_night) 预定订单间夜,
        round(sum(if( whc.order_type <> 3,income + compensate_income,0))/100) GMV,
        round(sum(if( whc.order_type <> 3,income+ compensate_income,0))/sum(if( whc.order_type <> 3,have_order,0))/100) 日价,
        round(((sum(if( whc.order_type <> 3,income + compensate_income,0))*0.94*0.7 - sum(if( whc.order_type <> 3,rent_day,0)))/100)) 总净利,
        round(sum(if( whc.order_type <> 3,income+ compensate_income,0))/sum(if((whc.online_stock=1 and whc.rent_stock=1) or (whc.have_order = 1 and whc.order_type <> 3),1,0))/100) REVPAR
        from youjia_report.wh_house_static whs
        left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
        where whc.dt between '{st}' and '{et}'
         and whs.own_type in {own_type}
        and whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
        group by whs.own_type,whs.city_name with rollup""".format(st=st, et=et, own_type=own_type)
    return pd.read_sql_query(sql, db_util.con_youjia)


def city_sale_data_last_year(st, et, own_type=(1, 2, 3)):
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    st = date_util.date_sub(st, years=1, days=0)
    et = date_util.date_sub(et, years=1, days=0)
    sql = """select 
        COALESCE(whs.city_name,'全国')  城市,
        case group_concat(distinct whs.own_type order by whs.own_type)
             when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
        concat(round(sum(have_order)/sum(if(online_stock=1  or have_order =1,1,0))*100),'%') 去年同期在线房源入住率,
        round(sum(income+compensate_income)/sum(have_order)/100) 去年同期日价,
        round(sum(income+compensate_income)/sum(if((whc.online_stock=1 and whc.rent_stock=1) or (whc.have_order = 1 ),1,0))/100) 去年同期REVPAR
        from youjia_report.wh_house_static whs
        left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
        where whc.dt between '{st}' and '{et}'
         and whs.own_type in {own_type}
        and (whc.online_stock = 1 or have_order = 1)
        group by whs.own_type,whs.city_name with rollup""".format(st=st, et=et, own_type=own_type)

    return pd.read_sql_query(sql, db_util.con_youjia)


def orders(st, et):
    sql = """select 
                case lh.own_type when 1 then '有家' when 2 then '城宿' when 3 then '斯维登' else '' end 公司,
                city.short_name 城市,
                concat(bo.id ,"") 订单编号,
                date(bo.checkin_day) 预定入住日期,
                date(bo.checkout_day)  预定退房日期,
                date(bo.actcheckin_day) 实际入住日期,
                date(bo.actcheckout_day) 实际退房日期,
                l.title 房源名称,
                concat(l.id,"") 房源编号,
                concat(od.house_id,"") 房屋编号,
                oe.tenant_name 房客姓名,
                oe.tenant_mobile 房客电话,
                sc.name 预订平台,
                 

                if(bo.cancel_status = 0 ,bo.real_income,bo.cut_part)/100 订单总额,
                case bo.cancel_status 
                when 0 then '未取消'
                when 1 then '房客取消支付超时'
                when 2 then '房客取消未付款'
                when 3 then '房客取消全额退款'
                when 4 then '房客取消部分扣款'
                when 5 then '房客取消全额扣款'
                when 6 then '管家拒绝订单取消'
                when 7 then '管家拒绝全额退款'
                when 8 then '管家拒绝确认超时'
                when 9 then '无房了'
                when 10 then '客服介入'
                else ""
                end 订单状态,

                ifnull(users1.realname,'线上销售为空') 线上销售,
                ifnull(users2.realname,'线下管家为空') 线下管家,
                if(bo.brush_status,"是","否") 是否刷单,
                date(bo.create_time) 添加时间,

                ct.score 评分,
                ct.content 评价

                from youjia.orders bo
                left join youjia.lodge l on l.id = bo.lodgeunit_id
                left join youjia.order_detail od on od.order_id = bo.id
                left join youjia.order_extend oe on oe.order_id = bo.id
                left join youjia_common.sales_channel sc on sc.id = bo.order_channel
                left join youjia.user_house_relation uhr1 on uhr1.house_id = od.house_id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
                LEFT JOIN youjia.users users1 ON uhr1.user_id=users1.id
                left join youjia.user_house_relation uhr2 on uhr2.house_id = od.house_id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
                LEFT JOIN youjia.users users2 ON uhr2.user_id=users2.id
                left join youjia.city on city.id = bo.city_id
                left join youjia.lod_house lh on lh.id = od.house_id 
                LEFT JOIN youjia.comment ct on ct.order_id = bo.id AND ct.comment_type=0
                where bo.city_id <> 376
                AND (bo.pay_state = 1  or (bo.pay_state = 0 and  order_channel=5))
                AND bo.confirm_status = 1 
                and bo.cancel_status = 0
                and date(bo.checkout_day) between '{st}' and '{et}'
                group by bo.id""".format(st=st, et=et)
    df = pd.read_sql_query(sql, db_util.con_youjia)
    return df


if __name__ == '__main__':
    df123 = zhenguo_tag('2019-08-08')
    print(df123)
